package cn.stylefeng.guns.core.util;

import org.apache.commons.lang.time.DateFormatUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.text.NumberFormat;
import java.util.*;

/**
 * 系统名称：济南服务外包公共服务平台
 * 概要：从EXCEL导入到数据库 
 * 创建人：FH 
 * 创建时间：2014年12月23日
 * @version
 */
public class ObjectExcelRead {

	/**
	 * @param filepath   文件路径
	 * @param startrow   开始行号
	 * @param startcol   开始列号
	 * @param sheetnum   sheet
	 * @return list
	 */
	@SuppressWarnings("resource")
	public static List<Object> readExcel(String filepath, int startrow, int startcol, int sheetnum) {
		try {
			File target = new File(filepath);
			FileInputStream fi = new FileInputStream(target);
			if(filepath.endsWith(".xls")){
				//2003版本
				return readXlsExcel(fi, startrow, startcol, sheetnum);
			}else if(filepath.endsWith(".xlsx")){
				//2017
				return readXlsxExcel(fi, startrow, startcol, sheetnum);
			}else{
				return null;
			}
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}
	
	/**
	 * 读取2003excel
	 * @param fi
	 * @param startrow
	 * @param startcol
	 * @param sheetnum
	 * @return
	 * @throws IOException
	 */
	public static  List<Object> readXlsExcel(FileInputStream fi,int startrow, int startcol, int sheetnum) throws IOException{
		List<Object> varList = new ArrayList<Object>();
		HSSFWorkbook wb = new HSSFWorkbook(fi);
		// sheet 从0开始
		HSSFSheet  sheet = wb.getSheetAt(sheetnum); 
		// 取得最后一行的行号
		int rowNum = sheet.getLastRowNum() + 1; 
		// 行循环开始
		for (int i = startrow; i < rowNum; i++) { 
			// 行
			HSSFRow row = sheet.getRow(i); 
			if(row!=null && !isRowEmpty(row)){
				Map varpd = new HashMap();
				// 每行的最后一个单元格位置
				int cellNum = row.getLastCellNum(); 
				// 列循环开始
				for (int j = startcol; j < cellNum; j++) { 
					@SuppressWarnings("deprecation")
					HSSFCell cell = row.getCell(Short.parseShort(j + ""));
					String cellValue = null;
					if (null != cell) {
						// 判断excel单元格内容的格式，并对其进行转换，以便插入数据库
						switch (cell.getCellType()) {
						case NUMERIC:
							if(HSSFDateUtil.isCellDateFormatted(cell)){
								Date date = cell.getDateCellValue();
								cellValue = DateFormatUtils.format(date, "yyyy/MM/dd");
							}else{
								NumberFormat nf = NumberFormat.getInstance();
								cellValue = nf.format(cell.getNumericCellValue());
								if (cellValue.indexOf(",") >= 0) {
									cellValue = cellValue.replace(",", "");
								}
							}
							break;
						case STRING:
							cellValue = cell.getStringCellValue();
							break;
						case FORMULA:
							cellValue = cell.getNumericCellValue() + "";
							break;
						case BLANK:
							cellValue = "";
							break;
						case BOOLEAN:
							cellValue = String.valueOf(cell.getBooleanCellValue());
							break;
						case ERROR:
							cellValue = String.valueOf(cell.getErrorCellValue());
							break;
						}
					} else {
						cellValue = "";
					}
					varpd.put("var" + j, cellValue);
				}
				varList.add(varpd);
			}
		}
		fi.close();
		return varList;
	}
	/**
	 * 读取2007excel
	 * @param fi
	 * @param startrow
	 * @param startcol
	 * @param sheetnum
	 * @return
	 * @throws IOException
	 */
	public static  List<Object> readXlsxExcel(FileInputStream fi,int startrow, int startcol, int sheetnum) throws IOException{
		List<Object> varList = new ArrayList<Object>();
		XSSFWorkbook  wb = new XSSFWorkbook (fi);
		// sheet 从0开始
		XSSFSheet  sheet = wb.getSheetAt(sheetnum); 
		// 取得最后一行的行号
		int rowNum = sheet.getLastRowNum() + 1; 
		// 行循环开始
		for (int i = startrow; i < rowNum; i++) { 
			// 行
			XSSFRow row = sheet.getRow(i); 
			if(row!=null && !isRowEmpty(row)){
				Map varpd = new HashMap();
				// 每行的最后一个单元格位置
				int cellNum = row.getLastCellNum(); 
				// 列循环开始
				for (int j = startcol; j < cellNum; j++) { 
					@SuppressWarnings("deprecation")
					XSSFCell cell = row.getCell(Short.parseShort(j + ""));
					String cellValue = null;
					if (null != cell) {
						// 判断excel单元格内容的格式，并对其进行转换，以便插入数据库
						switch (cell.getCellType()) { 
						case NUMERIC:
							if(HSSFDateUtil.isCellDateFormatted(cell)){
								Date date = cell.getDateCellValue();
								cellValue = DateFormatUtils.format(date, "yyyy/MM/dd");
							}else{
								NumberFormat nf = NumberFormat.getInstance();
								cellValue = nf.format(cell.getNumericCellValue());
								if (cellValue.indexOf(",") >= 0) {
									cellValue = cellValue.replace(",", "");
								}
							}
							break;
						case STRING:
							cellValue = cell.getStringCellValue();
							break;
						case FORMULA:
							cellValue = cell.getNumericCellValue() + "";
							break;
						case BLANK:
							cellValue = "";
							break;
						case BOOLEAN:
							cellValue = String.valueOf(cell.getBooleanCellValue());
							break;
						case ERROR:
							cellValue = String.valueOf(cell.getErrorCellValue());
							break;
						}
					} else {
						cellValue = "";
					}
					varpd.put("var" + j, cellValue);
				}
				varList.add(varpd);
			}
		}
		return varList;
	}
	
	
	 /**
	 * @param row 判断Excel表格的行为不为空
	 * @return
	 */
	public static boolean isRowEmpty(Row row){
	        for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
	            Cell cell = row.getCell(i);
	            if (cell != null && cell.getCellType() != CellType.BLANK){
	                return false;
	            }
	        }
	        return true;
	}
	

}
